package sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import beans.TaskBean;
import beans.TaskStatusTypesBean;
import beans.TaskStatusTypesItemBean;
import beans.TaskboardItemBean;
import beans.TaskboardViewBean;
import beans.TasksForUserStoryBean;

public class TasksForUserStoryDao {
	private DataAccess dataAccess = new DataAccess();
	
	public TasksForUserStoryBean getTasks(int userStoryId) throws ClassNotFoundException, SQLException {
		TasksForUserStoryBean tasksForUserStoryBean = new TasksForUserStoryBean();

		String query = "SELECT T.ID, T.NAME, T.DESCRIPTION, T.ESTIMATE, TS.ID AS STATUS_ID,"
				+ " TS.NAME AS STATUS_NAME, TM.ID AS TEAM_MEMBER_ID, TM.NAME AS TEAM_MEMBER_NAME"
				+ " FROM TASKS T LEFT OUTER JOIN TEAM_MEMBERS TM ON TM.ID = T.ASSIGNED_TO_ID"
				+ " LEFT OUTER JOIN TASK_STATUS TS ON TS.ID = T.STATUS_ID"
				+ " WHERE USER_STORY_ID = " + userStoryId 
				+ " ORDER BY T.ID";
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			TaskBean taskBean = new TaskBean();
			taskBean.setId(rset.getInt("T.ID"));
			taskBean.setName(rset.getString("T.NAME"));
			taskBean.setDescription(rset.getString("T.DESCRIPTION"));
			taskBean.setEstimate(rset.getFloat("T.ESTIMATE"));
			taskBean.setStatusId(rset.getInt("STATUS_ID"));
			taskBean.setStatusName(rset.getString("STATUS_NAME"));
			taskBean.setAssignedToId(rset.getInt("TEAM_MEMBER_ID"));
			taskBean.setAssignedTo(rset.getString("TEAM_MEMBER_NAME"));

			tasksForUserStoryBean.addRow(taskBean);
		}
		dataAccess.closeConnection();
		
		return tasksForUserStoryBean;
	}
	
	// return information for a single task
	public TaskBean getTaskInfo(int id) throws ClassNotFoundException, SQLException {
		TaskBean taskBean = new TaskBean();
		
		String query = "SELECT T.ID, T.NAME, T.DESCRIPTION, T.ESTIMATE, TS.ID AS STATUS_ID,"
				+ " TS.NAME AS STATUS_NAME, TM.ID AS TEAM_MEMBER_ID, TM.NAME AS TEAM_MEMBER_NAME"
				+ " FROM TASKS T LEFT OUTER JOIN TEAM_MEMBERS TM ON TM.ID = T.ASSIGNED_TO_ID"
				+ " LEFT OUTER JOIN TASK_STATUS TS ON TS.ID = T.STATUS_ID"
				+ " WHERE T.ID = " + id ;
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			taskBean.setId(rset.getInt("T.ID"));
			taskBean.setName(rset.getString("T.NAME"));
			taskBean.setDescription(rset.getString("T.DESCRIPTION"));
			taskBean.setEstimate(rset.getFloat("T.ESTIMATE"));
			taskBean.setStatusId(rset.getInt("STATUS_ID"));
			taskBean.setStatusName(rset.getString("STATUS_NAME"));
			taskBean.setAssignedToId(rset.getInt("TEAM_MEMBER_ID"));
			taskBean.setAssignedTo(rset.getString("TEAM_MEMBER_NAME"));
		}
		dataAccess.closeConnection();

		return taskBean;
	}
	
	public int insert(TaskBean taskBean) throws ClassNotFoundException, SQLException {
		String query = "INSERT INTO TASKS (NAME, DESCRIPTION, ESTIMATE, STATUS_ID, USER_STORY_ID, ASSIGNED_TO_ID)"
				+ " VALUES (?, ?, ?, ?, ?, ?)";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, taskBean.getName());
		preparedStatement.setString(2, taskBean.getDescription());
		if (taskBean.getEstimate() == 0) {
			preparedStatement.setNull(3, Types.FLOAT);
		}
		else {
			preparedStatement.setFloat(3, taskBean.getEstimate());
		}
		preparedStatement.setInt(4, taskBean.getStatusId());
		preparedStatement.setInt(5, taskBean.getUserStoryId());
		
		int assignedToId = taskBean.getAssignedToId();
		if (assignedToId == 0) {
			preparedStatement.setNull(6, Types.INTEGER);
		}
		else {
			preparedStatement.setInt(6, taskBean.getAssignedToId());
		}

		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get id of new data
			ResultSet rset = dataAccess.getSelectResult("SELECT LAST_INSERT_ID()");
			while (rset.next()) {
				taskBean.setId(rset.getInt(1));
			}
		}
		dataAccess.closeConnection();
			
		return rowsUpdated;
	}
	
	public int update(TaskBean taskBean) throws ClassNotFoundException, SQLException {
		String query = "UPDATE TASKS SET NAME=?, DESCRIPTION=?, ESTIMATE=?, STATUS_ID=?, ASSIGNED_TO_ID=?"
				+ " WHERE ID=?";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, taskBean.getName());
		preparedStatement.setString(2, taskBean.getDescription());
		if (taskBean.getEstimate() == 0) {
			preparedStatement.setNull(3, Types.FLOAT);
		}
		else {
			preparedStatement.setFloat(3, taskBean.getEstimate());
		}
		preparedStatement.setInt(4, taskBean.getStatusId());
					
		int assignedToId = taskBean.getAssignedToId();
		if (assignedToId == 0) {
			preparedStatement.setNull(5, Types.INTEGER);
		}
		else {
			preparedStatement.setInt(5, taskBean.getAssignedToId());
		}

		preparedStatement.setInt(6, taskBean.getId());
	
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// return data

			// need to get task_status_name and task_assigned_to_name
			ResultSet rset = dataAccess.getSelectResult("SELECT TS.NAME AS STATUS_NAME, TM.NAME AS ASSIGNED_TO_NAME"
					+ " FROM TASKS T LEFT JOIN TASK_STATUS TS ON T.STATUS_ID = TS.ID"
					+ " LEFT OUTER JOIN TEAM_MEMBERS TM ON T.ASSIGNED_TO_ID = TM.ID"
					+ " WHERE T.ID = " + taskBean.getId());
			
			while (rset.next()) {
				taskBean.setStatusName(rset.getString("STATUS_NAME"));
				taskBean.setAssignedTo(rset.getString("ASSIGNED_TO_NAME"));
			}
		}
		dataAccess.closeConnection();
			
		return rowsUpdated;
	}

	public int updateTaskStatus(int taskId, int status) throws ClassNotFoundException, SQLException {
		
		String query = "UPDATE TASKS SET STATUS_ID = " + status + " WHERE ID = " + taskId;

		int rowsUpdated = dataAccess.executeUpdate(query);
		dataAccess.closeConnection();
		
		return rowsUpdated;
	}
	
	public int delete(int id) throws ClassNotFoundException, SQLException {
		String query = "DELETE FROM TASKS WHERE ID = ?";
		int rowsUpdated = 0;

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, id);
		
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();

		return rowsUpdated;
	}

	public TaskboardViewBean getTaskboardViewInfo(int projectId) throws ClassNotFoundException, SQLException {
		TaskboardViewBean taskboardViewBean = null;
		
		// get data for a sprint that is contained by today's date
		
		String query = "SELECT U.ID as USER_STORY_ID, U.NAME AS USER_STORY_NAME,"
				+ " U.DESCRIPTION AS USER_STORY_DESCRIPTION,"
				+ " UST.TYPE_NAME, U.STORY_POINTS,"
				+ " T.ID AS TASK_ID, T.NAME AS TASK_NAME, T.DESCRIPTION AS TASK_DESCRIPTION,"
				+ " T.ESTIMATE, M.NAME AS ASSIGNED_TO,"
				+ " TS.ID AS STATUS_ID, TS.NAME AS STATUS_NAME"
				+ " FROM USER_STORIES U JOIN TASKS T ON U.ID = T.USER_STORY_ID"
				+ " JOIN USER_STORY_TYPE UST ON U.TYPE = UST.ID"
				+ " JOIN TASK_STATUS TS ON T.STATUS_ID = TS.ID"
				+ " LEFT OUTER JOIN TEAM_MEMBERS M ON T.ASSIGNED_TO_ID = M.ID"
				+ " JOIN SPRINTS SP ON U.SPRINT_ID = SP.ID"
				+ " WHERE U.PROJECT_ID = " + projectId
				+ " AND CURDATE() BETWEEN START_DATE AND END_DATE"
				+ " ORDER BY PRIORITY, TASK_ID";

		ResultSet rset = dataAccess.getSelectResult(query);
		
		taskboardViewBean = new TaskboardViewBean();
		while (rset.next()) {
			Integer storyPoints = rset.getInt("STORY_POINTS");
			if (rset.wasNull()) {
				storyPoints = null;
			}
			
			TaskboardItemBean taskboardItemBean = new TaskboardItemBean();
			taskboardItemBean.setUserStoryId(rset.getInt("USER_STORY_ID"));
			taskboardItemBean.setUserStoryName(rset.getString("USER_STORY_NAME"));
			taskboardItemBean.setUserStoryDescription(rset.getString("USER_STORY_DESCRIPTION"));
			taskboardItemBean.setUserStoryTypeName(rset.getString("TYPE_NAME"));
			taskboardItemBean.setUserStoryStoryPoints(storyPoints);
			taskboardItemBean.setTaskId(rset.getInt("TASK_ID"));
			taskboardItemBean.setTaskName(rset.getString("TASK_NAME"));
			taskboardItemBean.setTaskDescription(rset.getString("TASK_DESCRIPTION"));
			taskboardItemBean.setEstimate(rset.getFloat("ESTIMATE"));
			taskboardItemBean.setStatusId(rset.getInt("STATUS_ID"));
			taskboardItemBean.setStatusName(rset.getString("STATUS_NAME"));
			taskboardItemBean.setAssignedTo(rset.getString("ASSIGNED_TO"));
			
			taskboardViewBean.addRow(taskboardItemBean);
		}
		dataAccess.closeConnection();
		
		return taskboardViewBean;		
	}

	public TaskStatusTypesBean getTaskStatusTypes() throws ClassNotFoundException, SQLException {	
		TaskStatusTypesBean taskStatusTypesBean = new TaskStatusTypesBean();
		
		String query = "SELECT ID, NAME FROM TASK_STATUS ORDER BY ID";
	
		ResultSet rset;
		rset = dataAccess.getSelectResult(query);
	
		while (rset.next()) {
			TaskStatusTypesItemBean taskStatusTypesItemBean = new TaskStatusTypesItemBean();
			taskStatusTypesItemBean.setId(rset.getInt("ID"));
			taskStatusTypesItemBean.setName(rset.getString("NAME"));
			taskStatusTypesBean.addRow(taskStatusTypesItemBean);
		}
		dataAccess.closeConnection();
	
		return taskStatusTypesBean;
	}

}
